package com.xxx.DAO;

import com.mysql.jdbc.JDBC4PreparedStatement;
import com.xxx.entity.User;
import com.xxx.util.JDBCUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//数据库中tb_user表的dao
public class userDao {
    private JDBCUtil util = new JDBCUtil();
    //添加一行数据
    public int add(String userName,String userPwd)  {
        String sql = "insert into tb_user (userName,userPwd) values(?,?)";
        int result = 0;
        PreparedStatement ps = util.createPstmt(sql);
        try {
            ps.setString(1, userName);
            ps.setString(2, userPwd);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

    //删除数据行
    public int delete(String userName){
        String sql="delete from tb_user where userName=?";
        PreparedStatement ps=util.createPstmt(sql);
        int result=0;
        try {
            ps.setString(1,userName);
            result=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }

    //更新数据行
    public int update(String userName1,String userPwd,String userName2){
        String sql="update tb_user set userName=?,userPwd=? where userName=?";
        PreparedStatement ps=util.createPstmt(sql);
        int result=0;
        try {
            ps.setString(1,userName1);
            ps.setString(2,userPwd);
            ps.setString(3,userName2);
            result=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }

    //查询数据行
    //1.查询所有数据
    public List select(){
        String sql="select * from tb_user";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            rs=ps.executeQuery();
            while (rs.next()){
                int userId=rs.getInt("userId");
                String userName=rs.getString("userName");
                String userPwd=rs.getString("userPwd");
                String userIden=rs.getString("userIden");
                User user=new User(userId,userName,userPwd,userIden);
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }
    //2.根据name关键字模糊查询
    public List select(String userName,String userPwd){
        String sql="select * from tb_user where userName=? and userPwd=?";
        ResultSet rs=null;
        PreparedStatement ps=util.createPstmt(sql);
        List list=new ArrayList();
        try {
            ps.setString(1,userName);
            ps.setString(2,userPwd);
            rs=ps.executeQuery();
            while (rs.next()){
                int userId=rs.getInt("userId");
                String userName2=rs.getString("userName");
                String userPwd2=rs.getString("userPwd");
                String userIden=rs.getString("userIden");
                User user=new User(userId,userName2,userPwd2,userIden);
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }

    public userDao() {
    }
}
